use ION_Data

go

IF OBJECT_ID('dbo.GetReportYear') IS NOT NULL
 DROP PROC dbo.GetReportYear
GO

create procedure dbo.GetReportYear
as
begin	
	declare @sql varchar(max)
	declare @currentyear int
	declare @minyear int
	set @minyear = datepart(year, (select min(TimestampUTC) from DataLogStamp))
	
	set @currentyear = DATEPART(YEAR, GETDATE())	
	set @sql = 'SELECT ' + CAST(@currentyear as varchar(4)) + ' as [Year]'
	
	set @currentyear = @currentyear - 1
	while(@currentyear >= @minyear)
	begin
		set @sql = @sql + ' UNION SELECT ' + CAST(@currentyear as varchar(4))
		set @currentyear = @currentyear - 1		
	end	
	exec (@sql)	
end

go

IF OBJECT_ID('dbo.GetDevices') IS NOT NULL
 DROP PROC dbo.GetDevices
GO
create procedure dbo.GetDevices
as
begin
	select D.ID, D.Name, D.[Description], G.Name as GateName, G.[Description] as GateDesc
	from [ION_Data].[dbo].Source D
		join [ION_Network].[dbo].Device E on D.Name = E.Name
		join [ION_Network].[dbo].Port F on F.ID = E.PortID
		join [ION_Network].[dbo].Gate G on G.ID = F.GateID
	order by D.Name, D.[Description]
end

go

IF OBJECT_ID('dbo.GetDevicesOfStoreByName') IS NOT NULL
 DROP PROC dbo.GetDevicesOfStoreByName
GO
create procedure dbo.GetDevicesOfStoreByName
(
	@storeName varchar(MAX)
)
as
begin
	select D.ID, D.Name, D.[Description]
	from [ION_Data].[dbo].Source D
		join [ION_Network].[dbo].Device E on D.Name = E.Name
		join [ION_Network].[dbo].Port F on F.ID = E.PortID
		join [ION_Network].[dbo].Gate G on G.ID = F.GateID
	where D.Name LIKE @storeName + '.%'
end

go

IF OBJECT_ID('dbo.GetListTimeOfUse') IS NOT NULL
 DROP PROC dbo.GetListTimeOfUse
GO
create procedure dbo.GetListTimeOfUse
as
begin
	select A.TimeOfUse_ID AS TimeOfUseID, TimeOfUse_Name AS TimeOfUseName, B.Price, B.StoreName
	from [ION_Network].[dbo].PL_TOU_TimeOfUse A
		left join [ION_Network].[dbo].PL_TOU_PriceBoard B on A.TimeOfUse_ID = B.TimeOfUse_ID
end

go

IF OBJECT_ID('dbo.GetStoresByName') IS NOT NULL
 DROP PROC dbo.GetStoresByName
GO
create procedure dbo.GetStoresByName
as
begin
	select distinct dbo.GetStoreName(D.Name) StoreName, G.[Description]
	from [ION_Data].[dbo].Source D
		join [ION_Network].[dbo].Device E on D.Name = E.Name
		join [ION_Network].[dbo].Port F on F.ID = E.PortID
		join [ION_Network].[dbo].Gate G on G.ID = F.GateID
	order by dbo.GetStoreName(D.Name)
end

go

IF OBJECT_ID('dbo.GetStores') IS NOT NULL
 DROP PROC dbo.GetStores
GO
create procedure dbo.GetStores
as
begin
	select distinct G.ID, G.Name, G.[Description]
	from [ION_Data].[dbo].Source D
		join [ION_Network].[dbo].Device E on D.Name = E.Name
		join [ION_Network].[dbo].Port F on F.ID = E.PortID
		join [ION_Network].[dbo].Gate G on G.ID = F.GateID
end

go
IF OBJECT_ID('dbo.GetDevicesOfStore') IS NOT NULL
 DROP PROC dbo.GetDevicesOfStore
GO
create procedure dbo.GetDevicesOfStore
(
	@storeId int
)
as
begin
	select D.ID, D.Name, D.[Description]
	from [ION_Data].[dbo].Source D
		join [ION_Network].[dbo].Device E on D.Name = E.Name
		join [ION_Network].[dbo].Port F on F.ID = E.PortID
		join [ION_Network].[dbo].Gate G on G.ID = F.GateID
	where G.ID = @storeId
end

go

/**********************************************************************
*
*	REPORT ACTIVE ENERGY DELIVERED DATA OF A STORE ON A DATE
*
***********************************************************************/
IF OBJECT_ID('dbo.GetAED_SummaryDataOfStoreByYear') IS NOT NULL
 DROP PROC dbo.GetAED_SummaryDataOfStoreByYear
GO
create procedure dbo.GetAED_SummaryDataOfStoreByYear
(
	@year varchar(4)
)
as
begin
	--declare @year varchar(4); set @year = '2012'
	declare @FromDate datetime; set @FromDate = '01/01/' + @year + ' 12:00:00 AM'
	declare @ToDate datetime; set @ToDate = '12/31/' + @year + ' 12:00:00 AM'
	select G.Name AS StoreName,
		DATEPART(MONTH, B.TimestampUTC) AS ADE_Month,
		dbo.CheckTimeOfUsePeriod( B.TimestampUTC ) AS Peek_Of_Time,
		MAX(A.Value) - MIN(A.Value) DataValue
	from [ION_Data].[dbo].DataLog A
		join [ION_Data].[dbo].DataLogStamp B on A.DataLogStampID = B.ID
		join [ION_Data].[dbo].Source D on B.SourceID = D.ID
		join [ION_Network].[dbo].Device E on D.Name = E.Name
		join [ION_Network].[dbo].Port F on F.ID = E.PortID
		join [ION_Network].[dbo].Gate G on G.ID = F.GateID
	where A.QuantityID = 129
		AND CONVERT(varchar(10), B.TimestampUTC, 102) >= CONVERT(varchar(10), @FromDate, 102)
		AND CONVERT(varchar(10), B.TimestampUTC, 102) <= CONVERT(varchar(10), @ToDate, 102)	
	group by G.Name, G.[Description],
		DATEPART(MONTH, B.TimestampUTC),
		dbo.CheckTimeOfUsePeriod( B.TimestampUTC )
	order by G.Name
end

go

/**********************************************************************
*
*	REPORT ACTIVE ENERGY DELIVERED DATA OF A STORE ON A DATE
*
***********************************************************************/
IF OBJECT_ID('dbo.GetAED_DataOfStoreByPeriod') IS NOT NULL
 DROP PROC dbo.GetAED_DataOfStoreByPeriod
GO
create procedure dbo.GetAED_DataOfStoreByPeriod
(
	@StoreName varchar(MAX),
	@FromDate datetime,
	@ToDate datetime
)
as
begin
	select /*C.Name AS QtyName, */D.Name AS SourceName, D.[Description] AS SourceDesc,
		CONVERT(varchar(10), B.TimestampUTC, 103) AS DateUTC,		
		dbo.CheckTimeOfUsePeriod( B.TimestampUTC ) AS Peek_Of_Time,
		MAX(A.Value) - MIN(A.Value) DataValue
	from [ION_Data].[dbo].DataLog A
		join [ION_Data].[dbo].DataLogStamp B on A.DataLogStampID = B.ID
		join [ION_Data].[dbo].Quantity C on A.QuantityID = C.ID
		join [ION_Data].[dbo].Source D on B.SourceID = D.ID
		join [ION_Network].[dbo].Device E on D.Name = E.Name
	where A.QuantityID = 129
		AND CONVERT(varchar(10), B.TimestampUTC, 102) >= CONVERT(varchar(10), @FromDate, 102)
		AND CONVERT(varchar(10), B.TimestampUTC, 102) <= CONVERT(varchar(10), @ToDate, 102)
		AND D.Name LIKE @StoreName + '.%'
	group by /*C.Name, */D.Name, D.[Description],
		CONVERT(varchar(10), B.TimestampUTC, 103),		
		dbo.CheckTimeOfUsePeriod( B.TimestampUTC )
	order by D.Name
end

go

/**********************************************************************
*
*	REPORT ACTIVE ENERGY DELIVERED DATA OF A STORE ON A DATE
*
***********************************************************************/
IF OBJECT_ID('dbo.GetAED_DataOfStoreOnDate') IS NOT NULL
 DROP PROC dbo.GetAED_DataOfStoreOnDate
GO
create procedure dbo.GetAED_DataOfStoreOnDate
(
	@StoreName varchar(MAX),
	@CheckedDate datetime
)
as
begin
	select /*C.Name AS QtyName, */D.Name AS SourceName, D.[Description] AS SourceDesc,
		CONVERT(varchar(10), B.TimestampUTC, 103) AS DateUTC,		
		dbo.CheckTimeOfUsePeriod( B.TimestampUTC ) AS Peek_Of_Time,
		MAX(A.Value) - MIN(A.Value) DataValue
	from [ION_Data].[dbo].DataLog A
		join [ION_Data].[dbo].DataLogStamp B on A.DataLogStampID = B.ID
		join [ION_Data].[dbo].Quantity C on A.QuantityID = C.ID
		join [ION_Data].[dbo].Source D on B.SourceID = D.ID
		join [ION_Network].[dbo].Device E on D.Name = E.Name
	where A.QuantityID = 129
		AND CONVERT(varchar(10), B.TimestampUTC, 102) = CONVERT(varchar(10), @CheckedDate, 102)
		AND D.Name LIKE @StoreName + '.%'
	group by /*C.Name, */D.Name, D.[Description],
		CONVERT(varchar(10), B.TimestampUTC, 103),		
		dbo.CheckTimeOfUsePeriod( B.TimestampUTC )
	order by D.Name
end

go

/**********************************************************************
*
*	CHECK TIME OF USE OF LOG TIME DATA
*
***********************************************************************/
IF OBJECT_ID('dbo.CheckTimeOfUsePeriod') IS NOT NULL
 DROP FUNCTION dbo.CheckTimeOfUsePeriod
GO
create function dbo.CheckTimeOfUsePeriod ( @checkedDate as datetime ) returns int
as
begin
	declare @timeofuse smallint
	set @timeofuse = (	
		select top 1 TimeOfUse_ID 
		from [ION_Network].[dbo].PL_TOU_TimePeriod
		where (convert(varchar(5), Start_Time, 108) <= convert(varchar(5), @checkedDate, 108))
			and 1 = (
				case when datepart(hour, End_Time) = 0 and datepart(minute, End_Time) = 0 
				then 1
				else 
					case when convert(varchar(5), @checkedDate, 108) <= convert(varchar(5), End_Time, 108)
					then 1 else 0 end
				end
			)
			and DayGroup_ID = dbo.CheckDayGroups(@checkedDate)
	)
	return @timeofuse
end

go

/**********************************************************************
*
*	CHECK DAY GROUP IS WEEKDAYS OR WEEKENDS
*
***********************************************************************/
IF OBJECT_ID('dbo.CheckDayGroups') IS NOT NULL
 DROP FUNCTION dbo.CheckDayGroups
GO
create function dbo.CheckDayGroups( @CheckedDate DateTime ) returns int
as
begin
	declare @daygroup int
	
	declare @compare int
	declare @weekday int
	set @weekday = datepart(WEEKDAY, @CheckedDate)
	set @compare = (
		case @weekday
			when 1 /*Monday*/		then 40
			when 2 /*Tuesday*/		then 20
			when 3 /*Wednesday*/	then 10
			when 4 /*Thursday*/		then 8
			when 5 /*Friday*/		then 4
			when 6 /*Saturday*/		then 2
			when 7 /*Sunday*/		then 1
		else 0 end
	)
	set @daygroup = (select top 1 DayGroup_ID from [ION_Network].[dbo].PL_TOU_DayGroup where (@compare & IncludeDays) = @compare)	
	return @daygroup
end

go
IF OBJECT_ID('dbo.GetStoreName') IS NOT NULL
 DROP FUNCTION dbo.GetStoreName
GO
create function dbo.GetStoreName( @inputName varchar(MAX) ) returns varchar(MAX)
as
begin
	declare @results varchar(MAX)
	if(charindex('.', @inputName) > 0)
		set @results = substring(@inputName, 0, charindex('.', @inputName))
	else
		set @results = @inputName
		
	return @results
end

